package tbg;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Scanner;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ExcelMain {
	private Workbook wb;
	private Sheet sheet;
	private Row row;
	private static List<String> sss = new ArrayList();

	public List<List> readExcelContent(InputStream is) {
		List content = new ArrayList();

		String str = "";
		try {
			this.wb = WorkbookFactory.create(is);
		} catch (IOException e) {
			System.out.println("IOException");
			e.printStackTrace();
		} catch (InvalidFormatException e) {
			System.out.println("InvalidFormatException");
			e.printStackTrace();
		}
		this.sheet = this.wb.getSheetAt(0);

		int rowNum = this.sheet.getLastRowNum();
		this.row = this.sheet.getRow(0);

		int colNum = this.row.getPhysicalNumberOfCells();

		for (int i = 0; i <= rowNum; i++) {
			List rl = new ArrayList();
			this.row = this.sheet.getRow(i);
			int j = 0;
			int x = 0;
			while (j < colNum) {
				if (i == 0) {
					sss.add(getCellFormatValue(this.row.getCell((short) j)));
					j++;
				} else {
					if (j == colNum - 1) {
						x = Integer.parseInt(getCellFormatValue(this.row.getCell((short) j)).split("\\.")[0]);
						String v = "1";
						if (x < 0) {
							v = "-1";
							x = 0 - x;
						}
						rl.add(v);
					} else {
						rl.add(getCellFormatValue(this.row.getCell((short) j)));
					}
					j++;
				}
			}
			for (int m = 0; m < x; m++) {
				content.add(rl);
			}

			str = "";
		}
		return content;
	}

	public void editExcel(List<List> list, String fileName) {
		int cellnum = ((List) list.get(0)).size();
		int counter = 1;
		try {
			// Workbook wb = new SXSSFWorkbook();
			Sheet sheet1 = this.wb.createSheet("整理");
			Row row0 = sheet1.createRow(0);

			for (int i = 0; i < cellnum; i++) {
				Cell cell = row0.createCell(i);
				cell.setCellValue((String) sss.get(i));
			}
			for (int i = 0; i < list.size(); i++) {
				Row row = sheet1.createRow(i + 1);
				System.out.println(counter++);
				for (int j = 0; j < cellnum; j++) {
					Cell cell = row.createCell(j);
					cell.setCellValue((String) ((List) list.get(i)).get(j));
				}
			}

			OutputStream fileoutputstream = new FileOutputStream(fileName);
			wb.write(fileoutputstream);
			fileoutputstream.flush();
			fileoutputstream.close();
		} catch (Exception e) {
			System.out.println(e.getStackTrace());
			System.out.println(e.getMessage());
		}
	}

	private String getCellFormatValue(Cell cell) {
		String cellvalue = "";
		if (cell != null) {
			switch (cell.getCellType()) {
			case 0:
			case 2:
				if (DateUtil.isCellDateFormatted(cell)) {
					Date date = cell.getDateCellValue();
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					cellvalue = sdf.format(date);

					break;
				}
				cellvalue = String.valueOf(cell.getNumericCellValue());
				break;
			case 1:
				cellvalue = cell.getRichStringCellValue().getString();
				break;
			default:
				cellvalue = " ";

				break;
			}
		} else
			cellvalue = "";

		return cellvalue;
	}

	public static void main(String[] args) {
		System.out.println("拖动待处理文件到命令窗口： ");
		Scanner s = new Scanner(System.in);
		String fromfile = s.nextLine();
		System.out.println();
		try {
			FileInputStream fileinputstream = new FileInputStream(fromfile);

			ExcelMain excelMain = new ExcelMain();

			List list = excelMain.readExcelContent(fileinputstream);

			excelMain.editExcel(list, fromfile);
			fileinputstream.close();
			Thread.sleep(700L);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}